/*******************************************************************************
* Purpose: State-level goverment expenditures per capita
* 
* Dependencies:
*   Input: 1. Excel spreadsheet with state-level population in 1986 
*		   Retrieved from: https://fred.stlouisfed.org/release/tables?rid=118&eid=259194&od=1986-01-01#
*		   
*		   2. "Federal Expenditures by State for Fiscal Year 1985"
*		   Retrieved from: https://www2.census.gov/library/publications/1986/governments/fes-85.pdf
*		   
*		   3. Average CPI for 2020 and 1985
*		   Retrieved from: https://www.minneapolisfed.org/about-us/monetary-policy/inflation-calculator/consumer-price-index-1913- 
*   
*	Output: Stata file with governmnet expenditures per capita for each 
*			participating state (gpc.dta)
*
* Notes: The 1986 population numbers were used as they are closer to the the end 
*		 of FY85.
*******************************************************************************/
set more off
clear all

global storage: env storage

import excel "$storage/ece_parenting/us_1985/fred-us-population.xlsx", ///
	   sheet("Annual") firstrow clear
reshape long @POP, i(observation_date) j(state) string
rename POP population

keep if observation_date == "1986-01-01"

// add goverment expenditures from 
// Table 1 of "Federal Expenditures by State for Fiscal Year 1985"
gen expenditure = .
replace expenditure = 6873000000 	if state == "AR" // Arkansas
replace expenditure = 13452000000 	if state == "CT" // Connecticut
replace expenditure = 37384000000	if state == "FL" // Florida
replace expenditure = 23167000000 	if state == "MA" // Massachusetts
replace expenditure = 56773000000 	if state == "NY" // New York
replace expenditure = 35227000000 	if state == "PA" // Pennsylvania
replace expenditure = 44065000000 	if state == "TX" // Texas
replace expenditure = 16670000000 	if state == "WA" // Washington

local cpi_2020 = 258.812
local cpi_1985 = 107.6

gen gpc_1985 = expenditure / population
gen gpc = gpc_1985 * (`cpi_2020' / `cpi_1985') / 1000

// change state variable for merge
gen site = state
replace site = "ARK" if site == "AR"
replace site = "EIN" if site == "NY" 
replace site = "HAR" if site == "MA" 
replace site = "MIA" if site == "FL" 
replace site = "PEN" if site == "PA" 
replace site = "TEX" if site == "TX" 
replace site = "WAS" if site == "WA" 
replace site = "YAL" if site == "CT" 

drop observation_date state population expenditure gpc_1985

rename *, upper

cd $storage/ece_parenting/
capture mkdir "intermediate_data/"
capture mkdir "intermediate_data/us_1985"
save "$storage/ece_parenting/intermediate_data/us_1985/gpc.dta"
